-- 导入luasql.mysql模块(luarocks install luasql-mysql)
local mysql = require "luasql.mysql"

-- 创建MySQL连接
local env = mysql.mysql()
local conn = env:connect("awesome", "root", "123456", "localhost", 3306)

-- 插入商品信息
function insertProduct(name, price)
    conn:execute("INSERT INTO products (name, price) VALUES ('" .. name .. "', " .. price .. ")")
end

-- 删除商品信息
function deleteProduct(id)
    conn:execute("DELETE FROM products WHERE id = " .. id)
end

-- 更新商品价格
function updateProductPrice(id, newPrice)
    conn:execute("UPDATE products SET price = " .. newPrice .. " WHERE id = " .. id)
end

-- 查询商品列表（分页）
function queryProductsByPage(page, pageSize)
    local offset = (page - 1) * pageSize
    local cursor = conn:execute("SELECT * FROM products LIMIT " .. offset .. "," .. pageSize)
    local products = {}
    for row in cursor:rows() do
        table.insert(products, { id = row.id, name = row.name, price = row.price })
    end
    return products
end

-- 开启事务
function beginTransaction()
    conn:execute("START TRANSACTION")
end

-- 提交事务
function commitTransaction()
    conn:execute("COMMIT")
end

-- 回滚事务
function rollbackTransaction()
    conn:execute("ROLLBACK")
end

-- 定义存储过程
conn:execute [[
CREATE PROCEDURE get_product_by_id(IN productId INT)
BEGIN
    SELECT * FROM products WHERE id = productId;
END
]]

-- 调用存储过程
function callGetProductById(productId)
    local stmt = conn:execute("CALL get_product_by_id(" .. productId .. ")")
    local product = stmt:fetch({}, "a")
    stmt:close()
    return product
end

-- 使用示例
insertProduct("iPhone 12", 799)
insertProduct("iPad Pro", 999)
deleteProduct(1)
updateProductPrice(2, 1099)
local products = queryProductsByPage(1, 10)

-- 关闭连接
conn:close()
